package com.efast.dmom.mgmt.service.excel.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;

import com.efast.cafe.framework.jdbc.JdbcDao;
import com.efast.cafe.framework.jdbc.bean.DBColumn;
import com.efast.cafe.framework.util.SpringWebUtils;
import com.efast.dmom.mgmt.bean.excel.ExcelRuleDataBean;
import com.efast.dmom.mgmt.dao.excel.ExcelUploadRuleConfigurationDao;
import com.efast.dmom.mgmt.dao.excel.QualityDataExcelUploadDao;
import com.efast.dmom.mgmt.service.excel.QualityDataExcelUploadService;
import com.efast.dmom.mgmt.utils.Assist;
import com.efast.dmom.mgmt.utils.ExcelUploadUtil;

import jodd.util.StringUtil;

@Service
public class QualityDataExcelUploadServiceImpl implements QualityDataExcelUploadService {

	@Autowired
	private QualityDataExcelUploadDao qualityDataExcelUploadDao;

	@Autowired
	private ExcelUploadRuleConfigurationDao excelUploadRuleConfigurationDao;

	@Autowired
	@Qualifier("jdbcDao")
	private JdbcDao jdbcDao;

	public  boolean isAllRowEmpty(Row row,Row firstRow) {
		int count = 0;
		//单元格数量
		int rowCount = firstRow.getLastCellNum() - firstRow.getFirstCellNum();
		//判断多少个单元格为空
		for (int c = 0; c < rowCount; c++) {
			Cell cell = row.getCell(c);
			if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK || StringUtil.isEmpty((cell+"").trim())){
				count += 1;
			}
		}
		if (count == rowCount) {
			return true;
		}
 
		return  false;
	}
	@Override
	public boolean IQCExcelUpload(File file) {
		try {
			// MultipartFile类型文件转换为file类型
//			File file = new File(mulfile.getOriginalFilename());
			// 文件流
			FileInputStream in = new FileInputStream(file);
			Workbook workbook;
			try {
				workbook = ExcelUploadUtil.getWorkbok(in, file);
				// Sheet的数量
				// 设置当前excel中sheet的下标：0开始
				int sheetCount = workbook.getNumberOfSheets();
				for (int i = 0; i < sheetCount; i++) {
					// 遍历第一个Sheet
					Sheet sheet = workbook.getSheetAt(0);
					// 定义一个存储map的集合
					Map<String, Object> map = new HashMap<String, Object>();
					// 查询规则表
					List<ExcelRuleDataBean> list = excelUploadRuleConfigurationDao
							.selectExcelRuleByTableName("tw_bas_iqc");
					// 获取查重字段
					List<ExcelRuleDataBean> ruleDataBean = new ArrayList<ExcelRuleDataBean>();
					for (ExcelRuleDataBean bean : list) {
						if (bean.getRepeatField() != null) {
							ExcelRuleDataBean dataBean = new ExcelRuleDataBean();
							dataBean.setFieldName(bean.getFieldName());
							dataBean.setFieldComment(bean.getFieldComment());
							dataBean.setExcelNumber(bean.getExcelNumber());
							dataBean.setRepeatField(bean.getRepeatField());
							ruleDataBean.add(dataBean);
						}
					}
					// 查询插入表的字段名
					List<String> list1 = new ArrayList<String>();
					List<DBColumn> dbColum = jdbcDao.getColumns("tw_bas_iqc");
					if (dbColum != null && dbColum.size() > 0) {
						for (DBColumn data : dbColum) {
							list1.add(data.getColumnName());
						}
					}
					// 为跳过几行数据设置count
					int count = 0;
					for (Row row : sheet) {
						// 默认时跳过第一行插入数据
						if (count < 2) {
							count++;
							continue;
						}
						//校验模板字段与规则字段是否一致
						if (count == 2) {
							for(ExcelRuleDataBean uploadRuleData : list) {
								if (!uploadRuleData.getFieldComment().equals(ExcelUploadUtil.getValue(row.getCell(uploadRuleData.getExcelNumber())))){
								return false;
								}
							}
							count++;
							continue;
						}
						if (isAllRowEmpty(row,sheet.getRow(0))) {
							continue;
						}
						// 遍历上传的字段数据
						for (ExcelRuleDataBean uploadRuleData : list) {
							Cell cell = row.getCell(uploadRuleData.getExcelNumber());
							if (cell == null || cell.equals("") || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
								// System.out.print("null" + "\t");
								continue;
							}
							// 获取对应列号的值
							Object obj = ExcelUploadUtil.getValue(cell);
							if ("".equals(obj)) {
								map.put(uploadRuleData.getFieldName(), null);
							} else {
								map.put(uploadRuleData.getFieldName(), obj);
							}
						}
						Assist assit = new Assist();
						Integer qty;
						if (ruleDataBean != null && ruleDataBean.size() > 0) {
							for (ExcelRuleDataBean data : ruleDataBean) {
								if (ExcelUploadUtil.getValue(row.getCell(data.getExcelNumber())) == null
										|| ExcelUploadUtil.getValue(row.getCell(data.getExcelNumber())).equals("")) {
									assit.setRequires(Assist.isEq(data.getFieldName()));
									continue;
								}
								assit.setRequires(Assist.andEq(data.getFieldName(),
										ExcelUploadUtil.getValue(row.getCell(data.getExcelNumber()))));
							}
							qty = qualityDataExcelUploadDao.verifyRepetitionField("tw_bas_iqc", assit);
						} else {
							qty = 0;
						}
						if (qty > 0) {
							if (list1 != null && list1.size() > 0) {
								for (String fieldName : list1) {
									if ("last_update_time".equals(fieldName)) {
										map.put("last_update_user", new Date());
									}
									if ("last_update_user".equals(fieldName)) {
										map.put("last_update_user", SpringWebUtils.getRequestUser());
									}
								}
							}
							qualityDataExcelUploadDao.updateEachTable("tw_bas_iqc", map, assit);
						} else {
							if (list1 != null && list1.size() > 0) {
								for (String fieldName : list1) {
									if ("create_time".equals(fieldName)) {
										map.put("create_time", new Date());
									}
									if ("create_user".equals(fieldName)) {
										map.put("create_user", SpringWebUtils.getRequestUser());
									}
									if ("company_code".equals(fieldName)) {
										map.put("company_code", SpringWebUtils.getRequestCompany());
									}
								}
							}
							qualityDataExcelUploadDao.insertEachTable("tw_bas_iqc", map);
						}
						map.clear();
					}
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}
		return true;
	}

	@Override
	public boolean afterQualityUpload(File file) {
		try {
			// MultipartFile类型文件转换为file类型
//			File file = new File(mulfile.getOriginalFilename());
			// 文件流
			FileInputStream in = new FileInputStream(file);
			Workbook workbook;
			try {
				workbook = ExcelUploadUtil.getWorkbok(in, file);
				// Sheet的数量
				// 设置当前excel中sheet的下标：0开始
				int sheetCount = workbook.getNumberOfSheets();
				for (int i = 0; i < sheetCount; i++) {
					// 遍历第一个Sheet
					Sheet sheet = workbook.getSheetAt(0);
					// 定义一个存储map的集合
					Map<String, Object> map = new HashMap<String, Object>();
					// 查询规则表，获取查重字段
					List<ExcelRuleDataBean> list = excelUploadRuleConfigurationDao
							.selectExcelRuleByTableName("tw_after_quality");
					// 获取查重字段
					List<ExcelRuleDataBean> ruleDataBean = new ArrayList<ExcelRuleDataBean>();
					for (ExcelRuleDataBean bean : list) {
						if (bean.getRepeatField() != null) {
							ExcelRuleDataBean dataBean = new ExcelRuleDataBean();
							dataBean.setFieldName(bean.getFieldName());
							dataBean.setFieldComment(bean.getFieldComment());
							dataBean.setExcelNumber(bean.getExcelNumber());
							dataBean.setRepeatField(bean.getRepeatField());
							ruleDataBean.add(dataBean);
						}
					}
					// 查询插入表的字段名
					List<String> list1 = new ArrayList<String>();
					List<DBColumn> dbColum = jdbcDao.getColumns("tw_after_quality");
					if (dbColum != null && dbColum.size() > 0) {
						for (DBColumn data : dbColum) {
							list1.add(data.getColumnName());
						}
					}
					// 为跳过几行数据设置count
					int count = 0;
					for (Row row : sheet) {
						// 默认时跳过第一行插入数据
						if (count < 2) {
							count++;
							continue;
						}
						if (count == 2) {
							for(int m=1;m<=17;m++) {
								if(m!=17) {
									if(!list.get(m-1).getFieldComment().equals(ExcelUploadUtil.getValue(row.getCell(m)))){
										return false;
									}
								}
								if(m == 17) {
									if(!list.get(24).getFieldComment().equals(ExcelUploadUtil.getValue(row.getCell(25)))){
										return false;
									}
								}
							}
							count++;
							continue;
						}
						if (count ==3) {
							for (int n =17;n<=24;n++) {
								if(!list.get(n-1).getFieldComment().equals(ExcelUploadUtil.getValue(row.getCell(n)))){
									return false;
								}
							}
							count++;
							continue;
						}
						if (isAllRowEmpty(row,sheet.getRow(0))) {
							continue;
						}
						// 遍历上传的字段数据
						for (ExcelRuleDataBean uploadRuleData : list) {
							Cell cell = row.getCell(uploadRuleData.getExcelNumber());
							if (cell == null) {
								// System.out.print("null" + "\t");
								continue;
							}
							// 获取对应列号的值
							Object obj = ExcelUploadUtil.getValue(cell);
							if ("".equals(obj)) {
								map.put(uploadRuleData.getFieldName(), null);
							} else {
								map.put(uploadRuleData.getFieldName(), obj);
							}
						}
						Assist assit = new Assist();
						Integer qty;
						if (ruleDataBean != null && ruleDataBean.size() > 0) {
							for (ExcelRuleDataBean data : ruleDataBean) {
								if (ExcelUploadUtil.getValue(row.getCell(data.getExcelNumber())) == null
										|| ExcelUploadUtil.getValue(row.getCell(data.getExcelNumber())).equals("")) {
									assit.setRequires(Assist.isEq(data.getFieldName()));
									continue;
								}
								assit.setRequires(Assist.andEq(data.getFieldName(),
										ExcelUploadUtil.getValue(row.getCell(data.getExcelNumber()))));
							}
							qty = qualityDataExcelUploadDao.verifyRepetitionField("tw_after_quality", assit);
						} else {
							qty = 0;
						}
						if (qty > 0) {
							if (list1 != null && list1.size() > 0) {
								for (String fieldName : list1) {
									if ("last_update_time".equals(fieldName)) {
										map.put("last_update_user", new Date());
									}
									if ("last_update_user".equals(fieldName)) {
										map.put("last_update_user", SpringWebUtils.getRequestUser());
									}
								}
							}
							qualityDataExcelUploadDao.updateEachTable("tw_after_quality", map,assit);
						} else {
							if (list1 != null && list1.size() > 0) {
								for (String fieldName : list1) {
									if ("create_time".equals(fieldName)) {
										map.put("create_time", new Date());
									}
									if ("create_user".equals(fieldName)) {
										map.put("create_user", SpringWebUtils.getRequestUser());
									}
									if ("company_code".equals(fieldName)) {
										map.put("company_code", SpringWebUtils.getRequestCompany());
									}
								}
							}
							qualityDataExcelUploadDao.insertEachTable("tw_after_quality", map);
						}
						map.clear();
					}
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}
		return true;

	}

}
